package com.eascs.workflow.wf.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.workflow.common.dao.BaseDao;
import com.eascs.workflow.common.entity.wf.ProcessTaskMain;
import com.eascs.workflow.common.entity.wf.ProcessTaskNode;
import com.eascs.workflow.common.entity.wf.TaskNodeAuditor;
import com.eascs.workflow.common.vo.ProcessTimeParam;
import com.eascs.workflow.common.vo.ProcessTimeVo;
import com.eascs.workflow.common.vo.UserTaskParam;
import com.eascs.workflow.common.vo.UserTaskVo;

@Repository
public class ProcessTaskMainDao extends BaseDao {
	private static final String TABLE_NAME = "wf_process_task_main";

	/**
	 * 获取贷款单下所有流程实例
	 * 
	 * @param referId
	 * @return
	 */
	public List<ProcessTaskMain> getTaskMainListByReferId(String referId) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? ORDER BY applied_date desc";
		return workflowJdbcSession.findEntitys(sql, ProcessTaskMain.class, referId);
	}

	/**
	 * 获取贷款单下已有实例的流程类型
	 * 
	 * @param referId
	 * @return
	 */
	public List<ProcessTaskMain> getTaskMainTypeListByReferId(String referId) {
		String sql = "select * from wf_process_task_main t where t.refer_id=? GROUP BY process_type ORDER BY applied_date ";
		return workflowJdbcSession.findEntitys(sql, ProcessTaskMain.class, referId);
	}

	/**
	 * 根据流程类型查询
	 * 
	 * @param referId
	 * @param processType
	 * @return
	 */
	public List<ProcessTaskMain> getTaskMainListByType(String referId, String processType) {
		String sql = "select * from wf_process_task_main t where t.refer_id=? and process_type = ? ORDER BY applied_date ";
		return workflowJdbcSession.findEntitys(sql, ProcessTaskMain.class, referId, processType);
	}

	public ProcessTaskMain getTaskMainByReferIdAndProcessId(String referId, String processId) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? and process_id = ?";
		return workflowJdbcSession.findEntity(sql, ProcessTaskMain.class, referId, processId);
	}

	/**
	 * 检查流程是否已有启动实例
	 * 
	 * @param referId
	 * @param processId
	 * @return
	 */
	public ProcessTaskMain checkProcessStart(String referId, String processId) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? and process_id = ? and process_status ="
				+ ProcessTaskMain.PROECESS_STATUS_HANDING + " limit 1";
		return workflowJdbcSession.findEntity(sql, ProcessTaskMain.class, referId, processId);
	}

	public List<ProcessTaskMain> getTaskMainList(String referId, String processId, int status) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? and process_id = ? and process_status = ?";
		return workflowJdbcSession.findEntitys(sql, ProcessTaskMain.class, referId, processId, status);
	}

	/**
	 * 获取最新的一条 驳回记录，兼容老数据 老数据有多条驳回记录
	 * 
	 * @param referId
	 * @param processId
	 * @return
	 */
	public ProcessTaskMain getNewlyTurnedDownTaskMain(String referId, String processId) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? and process_id = ? and process_status ="
				+ ProcessTaskMain.PROECESS_STATUS_TURNED_DOWN + " order by applied_date desc limit 1";
		return workflowJdbcSession.findEntity(sql, ProcessTaskMain.class, referId, processId);
	}

	/**
	 * 获取待办列表
	 * 
	 * @return
	 */
	public PageResult<UserTaskVo> queryUndoTaskPage(UserTaskParam param) {
		SqlPageQuery<UserTaskVo> pageQuery = new SqlPageQuery<UserTaskVo>(UserTaskVo.class, param.getPageSize(),
				param.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT node_auditor_id nodeAuditorId, process_id processId, tm.process_name processName, tm.process_type processType, tm.org_name orgName,tm.task_id taskId, tm.task_code taskCode,tm.refer_id referId,tm.refer_url referUrl,refer_code referCode,node_name nodeName,")
				.append("appliedby_name appliedbyName,applied_date appliedDate,tk.auditor_start_date auditorStartDate, auditor_name auditorName, auditor_no auditorNo, applied_des appliedDes,tn.remark,tn.node_overtime as nodeOvertime")
				.append(" FROM wf_process_task_main tm")
				.append(" LEFT JOIN wf_process_task_node tn ON tm.task_id=tn.task_id ")
				.append(" LEFT JOIN wf_task_node_auditors tk ON tn.task_node_id=tk.task_node_id ")
				.append(" WHERE tm.process_status!=" + ProcessTaskMain.PROECESS_STATUS_CLOSE)
				.append(" AND tk.is_display='" + TaskNodeAuditor.IS_DISPLAY_Y + "' ");
		sql.append(" and tk.auditor_status =" + TaskNodeAuditor.AUDITOR_STATUS_AUDITING + " and tn.node_status ="
				+ ProcessTaskNode.NODE_STATUS_AUDITING);
		if (StringUtil.hasText(param.getAppliedbyName())) {
			sql.append(" and tm.appliedby_name like ? ");
			pageQuery.addParam("%" + param.getAppliedbyName() + "%");
		}
		if (!CollectionUtils.isEmpty(param.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and tm.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < param.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(param.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(param.getReferCode())) {
			sql.append(" and tm.refer_code = ? ");
			pageQuery.addParam(param.getReferCode());
		}
		if (StringUtil.hasText(param.getTaskCode())) {
			sql.append(" and tm.task_code = ? ");
			pageQuery.addParam(param.getTaskCode());
		}
		if (StringUtil.hasText(param.getAuditorNo())) {
			sql.append(" and tk.auditor_no = ? ");
			pageQuery.addParam(param.getAuditorNo());
		}
		if (StringUtil.hasText(param.getAuditorName())) {
			sql.append(" and tk.auditor_name like ?");
			pageQuery.addParam("%" + param.getAuditorName() + "%");
		}
		if (StringUtil.hasText(param.getLender())) {
			sql.append(" and tm.applied_des LIKE ?");
			pageQuery.addLikeParam(param.getLender());
		}
		if (StringUtil.hasText(param.getPnodeName())) {
			sql.append(" and tn.node_name LIKE ?");
			pageQuery.addLikeParam(param.getPnodeName());
		}
		sql.append(" order by tk.auditor_start_date");
		pageQuery.setQuerySql(sql.toString());
		return workflowJdbcSession.findDataWithPage(pageQuery);
	}

	/**
	 * 获取已办列表
	 * 
	 * @return
	 */
	public PageResult<UserTaskVo> queryHasdoTaskPage(UserTaskParam param) {
		SqlPageQuery<UserTaskVo> pageQuery = new SqlPageQuery<UserTaskVo>(UserTaskVo.class, param.getPageSize(),
				param.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tm.task_code taskCode,tm.process_id processId, tm.process_name processName,")
				.append("tm.process_type processType,tm.org_name orgName,tm.task_id taskId,tm.refer_id referId,tm.refer_url referUrl,")
				.append("tm.refer_code referCode,tm.process_status processStatus,tm.appliedby_name appliedbyName,tm.applied_des appliedDes,")
				.append("tm.applied_date appliedDate,tm.auditor_stime auditorStime, tm.auditor_etime auditorEtime,tn.remark")
				.append(" FROM wf_process_task_main tm, wf_process_task_node tn, wf_task_node_auditors tk")
				.append(" WHERE tm.task_id=tn.task_id and tn.task_node_id=tk.task_node_id");
		sql.append(" and tk.auditor_status in(" + TaskNodeAuditor.AUDITOR_STATUS_AUDIT_PASS + ","
				+ TaskNodeAuditor.AUDITOR_STATUS_REJECT + "," + TaskNodeAuditor.AUDITOR_STATUS_TURNED_DOWN + ") ");
		if (StringUtil.hasText(param.getAuditorNo())) {
			sql.append(" and tk.auditor_no = ? ");
			pageQuery.addParam(param.getAuditorNo());
		}
		if (StringUtil.hasText(param.getAppliedbyName())) {
			sql.append(" and tm.appliedby_name like ? ");
			pageQuery.addParam("%" + param.getAppliedbyName() + "%");
		}
		if (!CollectionUtils.isEmpty(param.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and tm.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < param.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(param.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(param.getReferCode())) {
			sql.append(" and tm.refer_code = ? ");
			pageQuery.addParam(param.getReferCode());
		}
		if (StringUtil.hasText(param.getTaskCode())) {
			sql.append(" and tm.task_code = ? ");
			pageQuery.addParam(param.getTaskCode());
		}
		if (StringUtil.hasText(param.getAuditorName())) {
			sql.append(" and tk.auditor_name like ?");
			pageQuery.addParam("%" + param.getAuditorName() + "%");
		}
		if (StringUtil.hasText(param.getLender())) {
			sql.append(" and tm.applied_des LIKE ?");
			pageQuery.addLikeParam(param.getLender());
		}
		if (StringUtil.hasText(param.getPnodeName())) {
			sql.append(" and tn.node_name LIKE ?");
			pageQuery.addLikeParam(param.getPnodeName());
		}
		sql.append(" order by tn.auditor_end_date desc");

		pageQuery.setQuerySql(sql.toString());
		return workflowJdbcSession.findDataWithPage(pageQuery);
	}

	/**
	 * 查询用户已发起的流程
	 * 
	 * @param param
	 * @return
	 */
	public PageResult<UserTaskVo> queryAppliedbyTaskPage(UserTaskParam param) {
		SqlPageQuery<UserTaskVo> pageQuery = new SqlPageQuery<UserTaskVo>(UserTaskVo.class, param.getPageSize(),
				param.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tm.task_code taskCode,tm.process_id processId, tm.process_name processName,")
				.append("tm.process_type processType,tm.org_name orgName,tm.task_id taskId,tm.refer_id referId,tm.refer_url referUrl,")
				.append("tm.refer_code referCode,tm.process_status processStatus,tm.appliedby_name appliedbyName,tm.applied_des appliedDes,")
				.append("tm.applied_date appliedDate,tm.auditor_stime auditorStime, tm.auditor_etime auditorEtime,tn.remark")
				.append(" FROM wf_process_task_main tm, wf_process_task_node tn, wf_task_node_auditors tk")
				.append(" WHERE tm.task_id=tn.task_id and tn.task_node_id=tk.task_node_id");
		if (StringUtil.hasText(param.getAppliedby())) {
			sql.append(" and tm.appliedby = ? ");
			pageQuery.addParam(param.getAppliedby());
		}
		if (StringUtil.hasText(param.getAppliedbyName())) {
			sql.append(" and tm.appliedby_name like ? ");
			pageQuery.addParam("%" + param.getAppliedbyName() + "%");
		}
		if (!CollectionUtils.isEmpty(param.getProcessTypeList())) {
			StringBuffer typeBuffer = new StringBuffer(" and tm.process_type in ( ");
			List<Object> typeList = new ArrayList<Object>();
			for (int i = 0; i < param.getProcessTypeList().size(); i++) {
				typeBuffer.append("?,");
				typeList.add(param.getProcessTypeList().get(i));
			}
			String sqlTypesCon = typeBuffer.substring(0, typeBuffer.length() - 1) + ")";
			sql.append(sqlTypesCon);
			pageQuery.addParams(typeList);
		}
		if (StringUtil.hasText(param.getReferCode())) {
			sql.append(" and tm.refer_code = ? ");
			pageQuery.addParam(param.getReferCode());
		}
		if (StringUtil.hasText(param.getTaskCode())) {
			sql.append(" and tm.task_code = ? ");
			pageQuery.addParam(param.getTaskCode());
		}
		if (StringUtil.hasText(param.getAuditorName())) {
			sql.append(" and tk.auditor_name like ?");
			pageQuery.addParam("%" + param.getAuditorName() + "%");
		}
		if (StringUtil.hasText(param.getLender())) {
			sql.append(" and tm.applied_des LIKE ?");
			pageQuery.addLikeParam(param.getLender());
		}
		if (StringUtil.hasText(param.getPnodeName())) {
			sql.append(" and tn.node_name LIKE ?");
			pageQuery.addLikeParam(param.getPnodeName());
		}
		sql.append(" group by tm.refer_code order by tm.applied_date");
		pageQuery.setQuerySql(sql.toString());

		return workflowJdbcSession.findDataWithPage(pageQuery);
	}

	public int closeProcessByReferId(String referId) {
		String sql = "UPDATE wf_process_task_main SET process_status=" + ProcessTaskMain.PROECESS_STATUS_CLOSE
				+ " WHERE refer_id = ? and process_status in(" + ProcessTaskMain.PROECESS_STATUS_HANDING + ")";
		return workflowJdbcSession.execute(sql, referId);
	}

	public List<ProcessTaskMain> getHandingProcess(String referId) {
		String sql = "select * from " + TABLE_NAME + " where refer_id = ? and process_status in("
				+ ProcessTaskMain.PROECESS_STATUS_HANDING + ")";
		return workflowJdbcSession.findEntitys(sql, ProcessTaskMain.class, referId);
	}

	public String queryReferIdByNodeAuditorId(String nodeAuditorId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select tm.refer_id referId ")
				.append(" from wf_process_task_main tm,wf_process_task_node tn, wf_task_node_auditors tk ")
				.append(" where tm.task_id=tn.task_id and tn.task_node_id=tk.task_node_id")
				.append(" and tk.node_auditor_id=?");
		return workflowJdbcSession.findString(sql.toString(), nodeAuditorId);
	}

	public String queryTaskIdByNodeAuditorId(String nodeAuditorId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select task_id taskId from wf_process_task_node tn,wf_task_node_auditors tk  ")
				.append(" where tn.task_node_id=tk.task_node_id and tk.node_auditor_id=? ");
		return workflowJdbcSession.findString(sql.toString(), nodeAuditorId);
	}

	public PageResult<ProcessTimeVo> queryProcessLoanPage(ProcessTimeParam param) {
		String sql = "select DISTINCT(refer_id) 'loanapplyNo' from wf_process_task_main where 1=1";
		SqlPageQuery<ProcessTimeVo> pageQuery = new SqlPageQuery<ProcessTimeVo>(ProcessTimeVo.class,
				param.getPageSize(), param.getPageIndex());
		if (param.getAppliedDate() != null) {
			sql += " and applied_date BETWEEN ? AND  ADDTIME(?, '24:00:00')";
			Calendar c = Calendar.getInstance();
			c.setTime(param.getAppliedDate());
			c.set(Calendar.HOUR_OF_DAY, 0);
			c.set(Calendar.MINUTE, 0);
			c.set(Calendar.SECOND, 0);
			c.set(Calendar.MILLISECOND, 0);
			pageQuery.addParam(c.getTime());
			pageQuery.addParam(c.getTime());
		}
		pageQuery.setQuerySql(sql);
		return super.findDataWithPage(pageQuery);
	}

	public ProcessTaskMain getTaskMainListByTaskId(String taskId) {
		String sql = "select * from wf_process_task_main t where t.task_id=?";
		return workflowJdbcSession.findEntity(sql, ProcessTaskMain.class, taskId);
	}

	public List<Object[]> getTaskMainByNodeCode(String nodeCode, String provinceCode, String sDate, String eDate) {
		String sql = "SELECT m.refer_code,p.regional_name,m.org_name,m.appliedby_name,n.node_name,n.task_node_id ";
		sql += " from wf_process_task_node n LEFT JOIN wf_process_task_main m on n.task_id = m.task_id ";
		sql += " LEFT JOIN o2o_platform_regional p on m.org_id = p.platform_id where n.node_code = ? ";
		if (StringUtils.isNotBlank(provinceCode) && !StringUtils.equals(provinceCode, "-1")) {
			sql += " and p.province_code = " + provinceCode;
		}
		if (StringUtils.isNotBlank(sDate)) {
			sql += " and m.applied_date >= '" + sDate + " 00:00:00'";
		}
		if (StringUtils.isNotBlank(eDate)) {
			sql += " and m.applied_date <= '" + eDate + " 23:59:59'";
		}
		return workflowJdbcSession.findObjectsList(sql, nodeCode);
	}
}
